<?php
/**
 * RedCross Application
 *
 * @copyright Copyright (c) 2010, Kristian Stokke Tryggestad, Rune Vikestad, Andreas Flaten Wist, Simen Nicolai Echholt, Ole Henrik Jahren
 */

/**
 * Gateway for the statistics
 *
 * @category     RedCross
 * @package        Core_Gateway
 */
class Core_Gateway_Statistics extends RedCross_Gateway_GatewayAbstract
{
    const HOURS_SORTBY_NAME = 'navn';
    const HOURS_SORTBY_HOURS = 'hours';
    const HOURS_SORTBY_SHIFTS = 'shifts';
    
    const ASC = 'asc';
    const DESC = 'desc';
    
    public function __construct()
    {
//        $this->_class = 'ShiftRole';
//        $this->_modelPath = 'Statistics';
    }

    public function fetchHoursAndShiftsPerMember($filterData)
    {
        $query = Doctrine_Query::create()
            ->select('sr.member_id, m.first_name AS first_name, m.last_name AS last_name, u.id AS user_id')
            ->addSelect('(SUM(UNIX_TIMESTAMP(s.to_date) - UNIX_TIMESTAMP(s.appearance_time)) / 3600) AS hours')
            ->addSelect('COUNT(*) AS number_of_shifts')
            ->from('Core_Model_ShiftRole sr')
            ->leftJoin('sr.Member m ON m.id = sr.attended_member_id')
            ->leftJoin('sr.Shift s')
            ->leftJoin('m.User u ON m.id=u.member_id')
            ->where('sr.attended_member_id IS NOT NULL')
            ->groupBy('m.id');        
            
        if (array_key_exists('member_id', $filterData))
        {
            $query->addWhere('m.id = ?', $filterData['member_id']);
        } else
        {
            if (array_key_exists('member_name', $filterData))
            {
                $member_name = $filterData['member_name'];
                $query->addWhere('CONCAT(m.first_name, \' \', m.last_name) LIKE ?', '%'.$member_name.'%');
            }
        }
        
        $this->_addFromDateAndToDateToWhere($query,$filterData);
                
        if (array_key_exists('sortby', $filterData))
        {
            $sortOrder = false;
            $orderBy = false;
            if (array_key_exists('sortorder', $filterData))
            {
                $sortOrder = 
                    $filterData['sortorder'] === self::ASC ? 
                    self::ASC : self::DESC;
            }
            
            $sortBy = $filterData['sortby'];
            if ($sortBy === self::HOURS_SORTBY_NAME)
            {
                $sortOrder = !$sortOrder ? self::ASC : $sortOrder;
                $orderBy = "m.first_name $sortOrder, m.last_name $sortOrder";
            } elseif ($sortBy === self::HOURS_SORTBY_HOURS)
            {
                $sortOrder = !$sortOrder ? self::DESC : $sortOrder;
                $orderBy = 'hours ' . $sortOrder;
            } elseif ($sortBy === self::HOURS_SORTBY_SHIFTS)
            {
                $sortOrder = !$sortOrder ? self::DESC : $sortOrder;
                $orderBy = 'number_of_shifts ' . $sortOrder;
            }
        } else {
            $sortOrder = self::ASC;
            $orderBy = "m.first_name $sortOrder, m.last_name $sortOrder";
        }
        $query->orderBy($orderBy);
                
        return $query->fetchArray();
    }
    
    public function fetchInactiveMembers($filterData)
    {
    
    	// Date code is copied from the function _addFromDateAndToDataToWhere
    	// because of the subquery in this query. TODO: Need Doctrine 2 or newer, and 
    	// adding this as a separate subquery, which could use the function for this purpose
    	// From date - look for the last year if nothing else specified
        if (array_key_exists('from_date', $filterData)) {
            $fromDate = new RedCross_Date($filterData['from_date']);
        } else {
            $fromDate = new RedCross_Date(strtotime("-1 year"));
        }
        $fromDate->setHour(0);
        $fromDate->setMinute(0);
        $fromDate->setSecond(0);
        $fromDateStr = $fromDate->export();
            
        // End date - look until today if nothing else specified
        if (array_key_exists('to_date', $filterData)) {
            $toDate = new RedCross_Date($filterData['to_date']);
        } else {
            $toDate = new RedCross_Date();
        }
        
        $toDate->setHour(23);
        $toDate->setMinute(59);
        $toDate->setSecond(59);
        $toDateStr = $toDate->export();	
        
        // Select the members which hasn't had a shift role in the selected interval
    	$query = Doctrine_Query::create()
    			->select('m.id AS member_id, m.first_name, m.last_name, u.id AS user_id')
    			->from('Core_Model_Member m')
    			->leftJoin('m.User u ON m.id = u.member_id ')
    			->where("m.id NOT IN (
    				SELECT DISTINCT sr.attended_member_id
    				FROM Core_Model_ShiftRole sr
    				JOIN sr.Shift s ON sr.shift_id = s.id
    				WHERE
    					sr.attended_member_id IS NOT NULL AND
    					s.appearance_time BETWEEN '$fromDateStr' AND '$toDateStr'
    			)")
    			->andWhere('m.quit_date IS NULL');		// Haven't been registered as a quitter
        	
        // Sorting
        if (array_key_exists('sortorder', $filterData) && $filterData['sortorder'] === self::DESC)
        {
            $sortOrder = self::DESC;
        } else {
            $sortOrder = self::ASC;
        }
        $orderBy = "m.first_name $sortOrder, m.last_name $sortOrder";
        $query->orderBy($orderBy);
                
        return $query->fetchArray();
    }


    public function fetchTotalHoursAndShifts($filterData)
    {
        $query = Doctrine_Query::create()
            ->select('s.id, sr.id') //we're not gonna use these ids but if we don't select them, we get the wrong result 
            ->addSelect('COUNT(*) AS number_of_shifts_roles')    
            ->addSelect('(SUM(UNIX_TIMESTAMP(s.to_date) - UNIX_TIMESTAMP(s.appearance_time)) / 3600) AS hours')
            ->addSelect('COUNT(DISTINCT s.id) AS number_of_shifts')
            ->addSelect('MONTH(s.from_date) AS month')
            ->addSelect('YEAR(s.from_date) AS year')            

            ->from('Core_Model_ShiftRole sr')
            ->leftJoin('sr.Shift s')
            ->where('sr.attended_member_id IS NOT NULL')
            ->groupBy('year, month')
            ->orderBy('year, month');        
        
        $this->_addFromDateAndToDateToWhere($query,$filterData);
        return $query->fetchArray();
    }

    private function _addFromDateAndToDateToWhere($query,$filterData)
    {
		// From date - look for the last year if nothing else specified
        if (array_key_exists('from_date', $filterData)) {
            $fromDate = new RedCross_Date($filterData['from_date']);
        } else {
            $fromDate = new RedCross_Date(strtotime("-1 year"));
        }
        $fromDate->setHour(0);
        $fromDate->setMinute(0);
        $fromDate->setSecond(0);
        $fromDateStr = $fromDate->export();
        $query->addWhere('s.from_date >= ?', $fromDateStr);
            
        // End date - look until today if nothing else specified
        if (array_key_exists('to_date', $filterData)) {
            $toDate = new RedCross_Date($filterData['to_date']);
        } else {
            $toDate = new RedCross_Date();
        }
        
        $toDate->setHour(23);
        $toDate->setMinute(59);
        $toDate->setSecond(59);
        $toDateStr = $toDate->export();
        $query->addWhere('s.from_date <= ?', $toDateStr);
    }        
}
